// Licensed to the Apache Software Foundation (ASF) under one
// or more contributor license agreements.  See the NOTICE file
// distributed with this work for additional information
// regarding copyright ownership.  The ASF licenses this file
// to you under the Apache License, Version 2.0 (the
// "License"); you may not use this file except in compliance
// with the License.  You may obtain a copy of the License at
//
//   http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing,
// software distributed under the License is distributed on an
// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
// KIND, either express or implied.  See the License for the
// specific language governing permissions and limitations
// under the License.
suite("q08_spill") {
  sql """
    set enable_force_spill=true;
  """
  sql """
    set spill_min_revocable_mem=1;
  """
  sql """
    use regression_test_tpcds_sf1_unique_p1;
  """
  qt_q08 """
SELECT
  s_store_name
, sum(ss_net_profit)
FROM
  store_sales
, date_dim
, store
, (
   SELECT ca_zip
   FROM
     (
(
         SELECT substr(ca_zip, 1, 5) ca_zip
         FROM
           customer_address
         WHERE (substr(ca_zip, 1, 5) IN (
                '24128'
              , '57834'
              , '13354'
              , '15734'
              , '78668'
              , '76232'
              , '62878'
              , '45375'
              , '63435'
              , '22245'
              , '65084'
              , '49130'
              , '40558'
              , '25733'
              , '15798'
              , '87816'
              , '81096'
              , '56458'
              , '35474'
              , '27156'
              , '83926'
              , '18840'
              , '28286'
              , '24676'
              , '37930'
              , '77556'
              , '27700'
              , '45266'
              , '94627'
              , '62971'
              , '20548'
              , '23470'
              , '47305'
              , '53535'
              , '21337'
              , '26231'
              , '50412'
              , '69399'
              , '17879'
              , '51622'
              , '43848'
              , '21195'
              , '83921'
              , '15559'
              , '67853'
              , '15126'
              , '16021'
              , '26233'
              , '53268'
              , '10567'
              , '91137'
              , '76107'
              , '11101'
              , '59166'
              , '38415'
              , '61265'
              , '71954'
              , '15371'
              , '11928'
              , '15455'
              , '98294'
              , '68309'
              , '69913'
              , '59402'
              , '58263'
              , '25782'
              , '18119'
              , '35942'
              , '33282'
              , '42029'
              , '17920'
              , '98359'
              , '15882'
              , '45721'
              , '60279'
              , '18426'
              , '64544'
              , '25631'
              , '43933'
              , '37125'
              , '98235'
              , '10336'
              , '24610'
              , '68101'
              , '56240'
              , '40081'
              , '86379'
              , '44165'
              , '33515'
              , '88190'
              , '84093'
              , '27068'
              , '99076'
              , '36634'
              , '50308'
              , '28577'
              , '39736'
              , '33786'
              , '71286'
              , '26859'
              , '55565'
              , '98569'
              , '70738'
              , '19736'
              , '64457'
              , '17183'
              , '28915'
              , '26653'
              , '58058'
              , '89091'
              , '54601'
              , '24206'
              , '14328'
              , '55253'
              , '82136'
              , '67897'
              , '56529'
              , '72305'
              , '67473'
              , '62377'
              , '22752'
              , '57647'
              , '62496'
              , '41918'
              , '36233'
              , '86284'
              , '54917'
              , '22152'
              , '19515'
              , '63837'
              , '18376'
              , '42961'
              , '10144'
              , '36495'
              , '58078'
              , '38607'
              , '91110'
              , '64147'
              , '19430'
              , '17043'
              , '45200'
              , '63981'
              , '48425'
              , '22351'
              , '30010'
              , '21756'
              , '14922'
              , '14663'
              , '77191'
              , '60099'
              , '29741'
              , '36420'
              , '21076'
              , '91393'
              , '28810'
              , '96765'
              , '23006'
              , '18799'
              , '49156'
              , '98025'
              , '23932'
              , '67467'
              , '30450'
              , '50298'
              , '29178'
              , '89360'
              , '32754'
              , '63089'
              , '87501'
              , '87343'
              , '29839'
              , '30903'
              , '81019'
              , '18652'
              , '73273'
              , '25989'
              , '20260'
              , '68893'
              , '53179'
              , '30469'
              , '28898'
              , '31671'
              , '24996'
              , '18767'
              , '64034'
              , '91068'
              , '51798'
              , '51200'
              , '63193'
              , '39516'
              , '72550'
              , '72325'
              , '51211'
              , '23968'
              , '86057'
              , '10390'
              , '85816'
              , '45692'
              , '65164'
              , '21309'
              , '18845'
              , '68621'
              , '92712'
              , '68880'
              , '90257'
              , '47770'
              , '13955'
              , '70466'
              , '21286'
              , '67875'
              , '82636'
              , '36446'
              , '79994'
              , '72823'
              , '40162'
              , '41367'
              , '41766'
              , '22437'
              , '58470'
              , '11356'
              , '76638'
              , '68806'
              , '25280'
              , '67301'
              , '73650'
              , '86198'
              , '16725'
              , '38935'
              , '13394'
              , '61810'
              , '81312'
              , '15146'
              , '71791'
              , '31016'
              , '72013'
              , '37126'
              , '22744'
              , '73134'
              , '70372'
              , '30431'
              , '39192'
              , '35850'
              , '56571'
              , '67030'
              , '22461'
              , '88424'
              , '88086'
              , '14060'
              , '40604'
              , '19512'
              , '72175'
              , '51649'
              , '19505'
              , '24317'
              , '13375'
              , '81426'
              , '18270'
              , '72425'
              , '45748'
              , '55307'
              , '53672'
              , '52867'
              , '56575'
              , '39127'
              , '30625'
              , '10445'
              , '39972'
              , '74351'
              , '26065'
              , '83849'
              , '42666'
              , '96976'
              , '68786'
              , '77721'
              , '68908'
              , '66864'
              , '63792'
              , '51650'
              , '31029'
              , '26689'
              , '66708'
              , '11376'
              , '20004'
              , '31880'
              , '96451'
              , '41248'
              , '94898'
              , '18383'
              , '60576'
              , '38193'
              , '48583'
              , '13595'
              , '76614'
              , '24671'
              , '46820'
              , '82276'
              , '10516'
              , '11634'
              , '45549'
              , '88885'
              , '18842'
              , '90225'
              , '18906'
              , '13376'
              , '84935'
              , '78890'
              , '58943'
              , '15765'
              , '50016'
              , '69035'
              , '49448'
              , '39371'
              , '41368'
              , '33123'
              , '83144'
              , '14089'
              , '94945'
              , '73241'
              , '19769'
              , '47537'
              , '38122'
              , '28587'
              , '76698'
              , '22927'
              , '56616'
              , '34425'
              , '96576'
              , '78567'
              , '97789'
              , '94983'
              , '79077'
              , '57855'
              , '97189'
              , '46081'
              , '48033'
              , '19849'
              , '28488'
              , '28545'
              , '72151'
              , '69952'
              , '43285'
              , '26105'
              , '76231'
              , '15723'
              , '25486'
              , '39861'
              , '83933'
              , '75691'
              , '46136'
              , '61547'
              , '66162'
              , '25858'
              , '22246'
              , '51949'
              , '27385'
              , '77610'
              , '34322'
              , '51061'
              , '68100'
              , '61860'
              , '13695'
              , '44438'
              , '90578'
              , '96888'
              , '58048'
              , '99543'
              , '73171'
              , '56691'
              , '64528'
              , '56910'
              , '83444'
              , '30122'
              , '68014'
              , '14171'
              , '16807'
              , '83041'
              , '34102'
              , '51103'
              , '79777'
              , '17871'
              , '12305'
              , '22685'
              , '94167'
              , '28709'
              , '35258'
              , '57665'
              , '71256'
              , '57047'
              , '11489'
              , '31387'
              , '68341'
              , '78451'
              , '14867'
              , '25103'
              , '35458'
              , '25003'
              , '54364'
              , '73520'
              , '32213'
              , '35576'))
      )       INTERSECT (
         SELECT ca_zip
         FROM
           (
            SELECT
              substr(ca_zip, 1, 5) ca_zip
            , count(*) cnt
            FROM
              customer_address
            , customer
            WHERE (ca_address_sk = c_current_addr_sk)
               AND (c_preferred_cust_flag = 'Y')
            GROUP BY ca_zip
            HAVING (count(*) > 10)
         )  a1
      )    )  a2
)  v1
WHERE (ss_store_sk = s_store_sk)
   AND (ss_sold_date_sk = d_date_sk)
   AND (d_qoy = 2)
   AND (d_year = 1998)
   AND (substr(s_zip, 1, 2) = substr(v1.ca_zip, 1, 2))
GROUP BY s_store_name
ORDER BY s_store_name ASC
LIMIT 100
"""
}
